﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Net;
using System.IO;

/// <summary>
/// Summary description for DatabaseCreator
/// </summary>
public class DbCreator
{
#region Fields
    private string fileUrl=string.Empty;
    public string DBServer=string.Empty;
    string DBName =string.Empty;
    string DBCreateDataFile=string.Empty;
    private string SQL_CONNECTION_STRING = string.Empty;
        //= "Server=" + DBServer + ";" + "DataBase=;" + "Integrated Security=SSPI;"; 
    bool bolDidPreviouslyConnect = false;
    string connectionString = string.Empty;
    private int timeout = 600;
   
#endregion

	public string DatabaseCreator()
	{
        SQL_CONNECTION_STRING = "Server=" + DBServer + ";" + "DataBase=;" + "Integrated Security=SSPI;"; 
		//
		// TODO: Add constructor logic here
		//
        return SQL_CONNECTION_STRING;
	}


   public bool CreateInstanceDb(string strUname, string strPassword)
 {

     string strSQL = "SELECT [name] FROM Master..sysdatabases WHERE [name] = N'" + DBName + "'";
           //+  Constants.vbCrLf + "BEGIN" + Constants.vbCrLf + "EXEC dbo.clearDBUsers " + DBName + "" + Constants.vbCrLf + "DROP DATABASE " + DBName + "" + Constants.vbCrLf + "END" + Constants.vbCrLf + "CREATE DATABASE " + DBName;
     
   
    bool bolIsConnecting = true;
      while (bolIsConnecting) {
          try {
               // The SqlConnection class allows you to communicate with SQL Server.
               // The constructor accepts a connection string as an argument. This
               // connection string uses Integrated Security, which means that you
               // must have a login in SQL Server, or be part of the Administrators
               // group for this to work.
               SqlConnection northwindConnection = new SqlConnection(SQL_CONNECTION_STRING);
              
               // A SqlCommand object is used to execute the SQL commands.
               SqlCommand cmd = new SqlCommand(strSQL, northwindConnection);
              
               // Open the connection, execute the command, and close the
               // connection. It is more efficient to ExecuteNonQuery when data is
               // not being returned.
               northwindConnection.Open();
               cmd.ExecuteNonQuery();
               northwindConnection.Close();
              
               // Data has been successfully submitted, so break out of the loop
               // and close the status form.
               bolIsConnecting = false;
               bolDidPreviouslyConnect = true;
               //bolDidCreateTable = True
               return true;
           }
           catch (SqlException sqlExc) {
               return false;
               break; // TODO: might not be correct. Was : Exit While
           }
           catch (Exception exc) {
               if (connectionString == SQL_CONNECTION_STRING) {
               }
               else {
                   return false;
              }
           }
       }
       return true;
   }

  public bool CreateSQLDb(string  strServerName , string strDBName, string strUserName, string strPass)
  {
     DBName = strDBName;
     DBServer = strServerName;
     if (strUserName.Trim().Length > 0) {
         SQL_CONNECTION_STRING = "User ID=" + strUserName + ";Pwd=" + strPass + ";Data Source=" + strServerName + ";database=;Persist Security Info=False;";
     }
     else {
         SQL_CONNECTION_STRING = "Server=" + DBServer + ";" + "DataBase=;" + "Integrated Security=SSPI;";
     }
     connectionString = SQL_CONNECTION_STRING;
     if (CreateInstanceDb(strUserName, strPass)) {
         return true;
     }
     else {
         return false;
     }
  }

   
    

   public string ConnString
   {
        get 
        { 
            return connectionString; 
        }
        set 
        {
            connectionString = value;
            SQL_CONNECTION_STRING = value;
        }
    }

    

   public bool ExecuteSQLScript(string SQlFilePath, string ConStr)
   {
       SqlConnection conn = null;
      try {
           // read file
           fileUrl = SQlFilePath;
           System.Net.WebRequest request = System.Net.WebRequest.Create(fileUrl);
           using (StreamReader sr = new StreamReader(request.GetResponse().GetResponseStream())) {
              
              
               // Create new connection to database
               conn = new SqlConnection(ConStr);
              
               conn.Open();
              
               while (!sr.EndOfStream) 
               {
                  System.Text.StringBuilder sb = new System.Text.StringBuilder();
                   SqlCommand cmd = conn.CreateCommand();
                  
                   while (!sr.EndOfStream) 
                   {
                       string s = sr.ReadLine();
                       if (s != null && s.ToUpper().Trim().Equals("GO")) {
                           break; // TODO: might not be correct. Was : Exit While
                       }
                      
                       sb.AppendLine(s);
                   }
                  
                   // Execute T-SQL against the target database
                   cmd.CommandText = sb.ToString();
                   cmd.CommandTimeout = timeout;
                  
                      
                   cmd.ExecuteNonQuery();
               }
           }
           //' Done
           return true;
      }
      catch (Exception ex) {
          // Error
          return false;
    }
     finally {
     }
   }


}
